﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using VietStar.Library.Commom.SqlServer;
using Entity = VietStar.Library.Entity;


namespace VietStar.Library.Data
{
    public class Comment
    {
        public Comment()
        { }

        Connection db = new Connection();
        Entity.Comment comment = new Entity.Comment();

        /// <summary>
        /// insert new comment to database
        /// </summary>
        /// <param name="entity">comment entity</param>
        /// <returns>bool</returns>
        public bool Insert(Entity.Comment entity)
        {
            try
            {
                using (SqlConnection connection = db.ReadFromWebConfig())
                {
                    SqlCommand command = new SqlCommand("Comment_Insert", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@News", entity.News));
                    command.Parameters.Add(new SqlParameter("@Name", entity.Name));
                    command.Parameters.Add(new SqlParameter("@Address", entity.Address));
                    command.Parameters.Add(new SqlParameter("@Email", entity.Email));
                    command.Parameters.Add(new SqlParameter("@Phone", entity.Phone));
                    command.Parameters.Add(new SqlParameter("@Content", entity.Content));
                    command.Parameters.Add(new SqlParameter("@CreateDate", entity.CreateDate));
                    command.Parameters.Add(new SqlParameter("@Status", entity.Status));
                    command.ExecuteNonQuery();
                    //done
                    connection.Close();
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// update comment content
        /// </summary>
        /// <param name="entity">comment entity</param>
        /// <returns>bool</returns>
        public bool Update(Entity.Comment entity)
        {
            try
            {
                using (SqlConnection connection = db.ReadFromWebConfig())
                {
                    SqlCommand command = new SqlCommand("Comment_Update", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@Id", entity.Id));
                    command.Parameters.Add(new SqlParameter("@News", entity.News));
                    command.Parameters.Add(new SqlParameter("@Name", entity.Name));
                    command.Parameters.Add(new SqlParameter("@Address", entity.Address));
                    command.Parameters.Add(new SqlParameter("@Email", entity.Email));
                    command.Parameters.Add(new SqlParameter("@Phone", entity.Phone));
                    command.Parameters.Add(new SqlParameter("@Content", entity.Content));
                    command.Parameters.Add(new SqlParameter("@CreateDate", entity.CreateDate));
                    command.Parameters.Add(new SqlParameter("@Status", entity.Status));
                    command.ExecuteNonQuery();
                    //done
                    connection.Close();
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// delete comment
        /// </summary>
        /// <param name="Id">comment id</param>
        /// <returns>bool</returns>
        public bool Delete(int Id)
        {
            try
            {
                using (SqlConnection connection = db.ReadFromWebConfig())
                {
                    SqlCommand command = new SqlCommand("Comment_Delete", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@Id", Id));
                    command.ExecuteNonQuery();
                    //done
                    connection.Close();
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// comment collection
        /// </summary>
        /// <returns></returns>
        public List<Entity.Comment> GetAll()
        {
            List<Entity.Comment> list = new List<Entity.Comment>();
            try
            {
                using (SqlConnection connection = db.ReadFromWebConfig())
                {
                    SqlCommand command = new SqlCommand("Comment_All", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            list.Add(EntityFromReader(reader));
                        }
                    }
                }
            }
            catch (Exception ex)
            { }
            return list;
        }
        /// <summary>
        /// comment collection by id
        /// </summary>
        /// <param name="Id">comment id</param>
        /// <returns></returns>
        public List<Entity.Comment> GetById(int Id)
        {
            List<Entity.Comment> list = new List<Entity.Comment>();
            try
            {
                using (SqlConnection connection = db.ReadFromWebConfig())
                {
                    SqlCommand command = new SqlCommand("Comment_ById", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@Id", Id));
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            list.Add(EntityFromReader(reader));
                        }
                    }
                }
            }
            catch (Exception ex)
            { }
            return list;
        }
        /// <summary>
        /// read data from database
        /// </summary>
        /// <param name="reader">comment reader</param>
        /// <returns>comment entity</returns>
        private Entity.Comment EntityFromReader(IDataReader reader)
        {
            Entity.Comment entity = new Entity.Comment();
            if (!reader.IsClosed)
            {
                if (reader["Id"] != null)
                    entity.Id = (int)reader["Id"];
                if (reader["Name"] != null)
                    entity.Name = (string)reader["Name"];
                if (reader["Address"] != null)
                    entity.Address = (string)reader["Address"];
                if (reader["Email"] != null)
                    entity.Email = (string)reader["Email"];
                if (reader["Phone"] != null)
                    entity.Phone = (string)reader["Phone"];
                if (reader["Content"] != null)
                    entity.Content = (string)reader["Content"];
                if (reader["CreateDate"] != null)
                    entity.CreateDate = (DateTime)reader["CreateDate"];
                if (reader["Status"] != null)
                    entity.Status = (bool)reader["Status"];
            }
            return entity;
        }
    }
}